﻿Imports System.Xml
Imports Mysoft.Map.Data
Imports System.Data.SqlClient
Imports System.Data.Text
Imports System.Text


Public Class YearPlanMng

    '''上报公司年度预算
    '参数：strYearPlanGUID 版本GUID
    '参数：strBUGUID 上报公司GUID
    '参数：strUserName 上报人名称
    Public Function ReportYearPlanCompany(ByVal strYearPlanGUID As String, ByVal strBUGUID As String, ByVal strUserGUID As String, ByVal strUserName As String) As Boolean

        Dim strSQL As New StringBuilder
        Dim strIsEndCompany As String

        '判断要上报的公司是否是末级公司
        strIsEndCompany = MyDB.GetDataItemString("SELECT IsEndCompany FROM myBusinessUnit WHERE BUGUID='" & strBUGUID & "'")

        If String.IsNullOrEmpty(strIsEndCompany) Then
            Return False
        End If

        '将公司收入预算数据的状态设置为“审核中”
        strSQL.Append("UPDATE ys_YearPlan2Object SET ItemState='审核中',ReportedByGUID='" & strUserGUID & "',ReportedBy='" & strUserName & "',ReportDate=GetDate()" & _
                ",PlanAmountYear=temp.PlanAmountYear" & _
                ",PlanAmount1=temp.PlanAmount1" & _
                ",PlanAmount2=temp.PlanAmount2" & _
                ",PlanAmount3=temp.PlanAmount3" & _
                ",PlanAmount4=temp.PlanAmount4" & _
                ",PlanAmount5=temp.PlanAmount5" & _
                ",PlanAmount6=temp.PlanAmount6" & _
                ",PlanAmount7=temp.PlanAmount7" & _
                ",PlanAmount8=temp.PlanAmount8" & _
                ",PlanAmount9=temp.PlanAmount9" & _
                ",PlanAmount10=temp.PlanAmount10" & _
                ",PlanAmount11=temp.PlanAmount11" & _
                ",PlanAmount12=temp.PlanAmount12" & _
                " from (" & _
                       "SELECT " & _
                             "ISNULL(sum(PlanAmountYear),0) as PlanAmountYear," & _
                             "ISNULL(sum(PlanAmount1),0) as PlanAmount1," & _
                             "ISNULL(sum(PlanAmount2),0) as PlanAmount2," & _
                             "ISNULL(sum(PlanAmount3),0) as PlanAmount3," & _
                             "ISNULL(sum(PlanAmount4),0) as PlanAmount4," & _
                             "ISNULL(sum(PlanAmount5),0) as PlanAmount5," & _
                             "ISNULL(sum(PlanAmount6),0) as PlanAmount6," & _
                             "ISNULL(sum(PlanAmount7),0) as PlanAmount7," & _
                             "ISNULL(sum(PlanAmount8),0) as PlanAmount8," & _
                             "ISNULL(sum(PlanAmount9),0) as PlanAmount9," & _
                             "ISNULL(sum(PlanAmount10),0) as PlanAmount10," & _
                             "ISNULL(sum(PlanAmount11),0) as PlanAmount11," & _
                             "ISNULL(sum(PlanAmount12),0) as PlanAmount12 ")

        '末级公司只需要取销售收入数据
        If strIsEndCompany.ToUpper = "TRUE" Then
            strSQL.Append(" FROM ys_YearPlan2Object where BUGUID='" & strBUGUID & "' AND YearPlanGUID ='" & strYearPlanGUID & "' AND ItemType='销售收入' AND ItemState='已审核'")
        Else
            '区域公司只取子公司的收入数据
            strSQL.Append(" FROM ys_YearPlan2Object a " & _
                          "      INNER JOIN myBusinessUnit b ON a.ObjectGUID=b.BUGUID" & _
                          " WHERE YearPlanGUID='" & strYearPlanGUID & "' AND b.ParentGUID='" & strBUGUID & "' AND ItemType='公司收入'  AND ItemState='已审核'")
        End If
        strSQL.Append(") temp WHERE ItemType='公司收入' AND ObjectGUID = '" & strBUGUID & "' AND YearPlanGUID ='" & strYearPlanGUID & "'")
                             

        '将公司支出预算数据的状态设置为“审核中”
        strSQL.Append(";UPDATE ys_YearPlan2Object SET ItemState='审核中',ReportedByGUID='" & strUserGUID & "',ReportedBy='" & strUserName & "',ReportDate=GetDate()" & _
                ",PlanAmountYear=temp.PlanAmountYear" & _
                ",PlanAmount1=temp.PlanAmount1" & _
                ",PlanAmount2=temp.PlanAmount2" & _
                ",PlanAmount3=temp.PlanAmount3" & _
                ",PlanAmount4=temp.PlanAmount4" & _
                ",PlanAmount5=temp.PlanAmount5" & _
                ",PlanAmount6=temp.PlanAmount6" & _
                ",PlanAmount7=temp.PlanAmount7" & _
                ",PlanAmount8=temp.PlanAmount8" & _
                ",PlanAmount9=temp.PlanAmount9" & _
                ",PlanAmount10=temp.PlanAmount10" & _
                ",PlanAmount11=temp.PlanAmount11" & _
                ",PlanAmount12=temp.PlanAmount12" & _
                " from (" & _
                       "SELECT " & _
                             "ISNULL(sum(PlanAmountYear),0) as PlanAmountYear," & _
                             "ISNULL(sum(PlanAmount1),0) as PlanAmount1," & _
                             "ISNULL(sum(PlanAmount2),0) as PlanAmount2," & _
                             "ISNULL(sum(PlanAmount3),0) as PlanAmount3," & _
                             "ISNULL(sum(PlanAmount4),0) as PlanAmount4," & _
                             "ISNULL(sum(PlanAmount5),0) as PlanAmount5," & _
                             "ISNULL(sum(PlanAmount6),0) as PlanAmount6," & _
                             "ISNULL(sum(PlanAmount7),0) as PlanAmount7," & _
                             "ISNULL(sum(PlanAmount8),0) as PlanAmount8," & _
                             "ISNULL(sum(PlanAmount9),0) as PlanAmount9," & _
                             "ISNULL(sum(PlanAmount10),0) as PlanAmount10," & _
                             "ISNULL(sum(PlanAmount11),0) as PlanAmount11," & _
                             "ISNULL(sum(PlanAmount12),0) as PlanAmount12 ")
        '末级公司取部门费用、项目成本、项目税费数据
        If strIsEndCompany.ToUpper = "TRUE" Then
            strSQL.Append(" FROM ys_YearPlan2Object where BUGUID='" & strBUGUID & "' AND YearPlanGUID ='" & strYearPlanGUID & "' AND ItemType in ('部门费用','项目成本','项目税费') AND ItemState='已审核'")
        Else
            '非末级公司取部门费用和子公司支出数据
            strSQL.Append(" FROM ys_YearPlan2Object a " & _
                         "      INNER JOIN vys_myBusinessUnit b ON a.ObjectGUID=b.BUGUID" & _
                         " WHERE YearPlanGUID='" & strYearPlanGUID & "' AND a.ObjectGUID !='" & strBUGUID & "' AND (b.ParentGUID='" & strBUGUID & "' OR b.CompanyGUID='" & strBUGUID & "') AND ItemType in ('部门费用','公司支出')  AND ItemState='已审核'")
        End If
        strSQL.Append(") temp WHERE ItemType='公司支出' AND ObjectGUID = '" & strBUGUID & "' AND YearPlanGUID ='" & strYearPlanGUID & "'")

        Dim intRtn As Integer = MyDB.ExecSQL(strSQL.ToString())
        If intRtn > 0 Then
            Return True
        Else
            Return False
        End If

    End Function

    '取消公司整体上报年度预算
    '参数：strYearPlanGUID 版本GUID
    '参数：strBUGUID 公司GUID
    Public Function CancelReportYearPlanCompany(ByVal strYearPlanGUID As String, ByVal strBUGUID As String) As Boolean

        Dim strSQL As New StringBuilder

        '将公司收入预算数据的状态设置为“未审核”
        strSQL.Append("UPDATE ys_YearPlan2Object SET ItemState='未审核',ReportedByGUID=NULL,ReportedBy=NULL,ReportDate=NULL" & _
                ",PlanAmountYear=0" & _
                ",PlanAmount1=0" & _
                ",PlanAmount2=0" & _
                ",PlanAmount3=0" & _
                ",PlanAmount4=0" & _
                ",PlanAmount5=0" & _
                ",PlanAmount6=0" & _
                ",PlanAmount7=0" & _
                ",PlanAmount8=0" & _
                ",PlanAmount9=0" & _
                ",PlanAmount10=0" & _
                ",PlanAmount11=0" & _
                ",PlanAmount12=0" & _
                " WHERE ItemType='公司收入' AND ObjectGUID = '" & strBUGUID & "' AND YearPlanGUID ='" & strYearPlanGUID & "'")

        '将公司支出预算数据的状态设置为“未审核”
        strSQL.Append("UPDATE ys_YearPlan2Object SET ItemState='未审核',ReportedByGUID=NULL,ReportedBy=NULL,ReportDate=NULL" & _
                ",PlanAmountYear=0" & _
                ",PlanAmount1=0" & _
                ",PlanAmount2=0" & _
                ",PlanAmount3=0" & _
                ",PlanAmount4=0" & _
                ",PlanAmount5=0" & _
                ",PlanAmount6=0" & _
                ",PlanAmount7=0" & _
                ",PlanAmount8=0" & _
                ",PlanAmount9=0" & _
                ",PlanAmount10=0" & _
                ",PlanAmount11=0" & _
                ",PlanAmount12=0" & _
                " WHERE ItemType='公司支出' AND ObjectGUID = '" & strBUGUID & "' AND YearPlanGUID ='" & strYearPlanGUID & "'")
        Dim intRtn As Integer = MyDB.ExecSQL(strSQL.ToString())
        If intRtn > 0 Then
            Return True
        Else
            Return False
        End If

    End Function

    '审核未通过/取消审核/取消上报
    '参数：strYearPlan2ObjectGUID 要取消审核的预算对象GUID，多个对象之间用逗号(，)隔开，如：对象1GUID,对象2GUID
    '参数：strApproveRemarks 取消审核意见
    '返回值：如果成功--"OK"，否则--"FAIL"
    Public Function YearPlanApproveUnPass(ByVal strYearPlan2ObjectGUID As String, ByVal strApproveRemarks As String, ByVal strActiveName As String) As Boolean

        Dim strSQL As String
        Dim IntReturn As Integer

        '将GUID字符串转换为可用于SQL中的IN语句写法：将所有逗号替换为','，然后在GUID字符串前后各加一个单引号
        strYearPlan2ObjectGUID = "'" + strYearPlan2ObjectGUID.Replace(",", "','") + "'"

        Dim MyPublicMng As New PublicMNG
        '获取审核人、审核日期的审核抬头信息
        Dim strApproveSeparator As String = MyPublicMng.GetApproveSeparator(strActiveName)

        strSQL = " UPDATE ys_YearPlan2Object SET " & _
                    " ApproveRemarks = '" & strApproveSeparator & Chr(13) & Chr(10) & strApproveRemarks & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "' + Convert(varchar(6000), IsNull(ApproveRemarks, '')), " & _
                    " ItemState = '未审核'," & _
                    " ReportedByGUID = NULL," & _
                    " ReportedBy = NULL," & _
                    " ReportDate = NULL," & _
                    " PlanAmountYear = 0.00," & _
                    " PlanAmount1 = 0.00," & _
                    " PlanAmount2 = 0.00," & _
                    " PlanAmount3 = 0.00," & _
                    " PlanAmount4 = 0.00," & _
                    " PlanAmount5 = 0.00," & _
                    " PlanAmount6 = 0.00," & _
                    " PlanAmount7 = 0.00," & _
                    " PlanAmount8 = 0.00," & _
                    " PlanAmount9 = 0.00," & _
                    " PlanAmount10 = 0.00," & _
                    " PlanAmount11 = 0.00," & _
                    " PlanAmount12 = 0.00 " & _
                 " WHERE YearPlan2ObjectGUID  in (" & strYearPlan2ObjectGUID & ")"

        '提交 SQL 语句
        IntReturn = MyDB.ExecSQL(strSQL)

        If IntReturn > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

End Class